# -*- coding: utf-8 -*-
"""
Created on Tue Dec 10 13:31:33 2024

@author: huangyue
"""





'''
获取构建转债策略所需要的原始数据
'''



import pymssql
# import argparse
# import ZSFundRequestClient as zs
# import sys
import datetime
import pandas as pd
# import numpy as np
# from numpy import nan

from .getCBData_jydb import get_CBinfo         # 提取需要分析的转债信息
from .getCBData_jydb import get_ZZZZ_data         # 提取转债指数
from .getCBData_jydb import get_ratingdata         # 提取转债评级
from .getCBData_jydb import get_IndustryInfo         # 提取转债行业
from .getCBData_jydb import get_enddatedata         # 提取到期日
from .getCBData_infodb import get_exsiteddata_from_FI_CBSeriesIndicator   # 提取原始数据

# from CBtimingStrategy import CBtimeingStrategy


# from utils import profit_analysis

import matplotlib.pyplot as plt
plt.rcParams['font.sans-serif'] = ['SimHei']  # 用来正常显示中文标签
plt.rcParams['axes.unicode_minus'] = False  # 用来正常显示负号

# 连接聚源的参数
server_jydb = "10.10.0.102"
user_jydb = "jydb"
password_jydb = "jydb"

# 连接infodb的参数
server_zs = "infodb"

d1 = datetime.timedelta(days=1)


# %% 函数：策略相关

# 回归
def get_inverse_para(data):
    # 最小二乘法求解反比例函数
    data['y/x'] = data['y']/data['x']
    data['1/x'] = 1/data['x']
    data['1/x2'] = 1/(data['x']**2)
    tmpsum = data.sum()
    n = data.shape[0]
    a = (tmpsum['1/x'] * tmpsum['y/x'] - tmpsum['1/x2'] * tmpsum['y'])/\
        (tmpsum['1/x'] * tmpsum['1/x'] - tmpsum['1/x2'] * n)
    b = (tmpsum['1/x'] * tmpsum['y'] - n * tmpsum['y/x'])/\
        (tmpsum['1/x'] * tmpsum['1/x'] - tmpsum['1/x2'] * n)
    return a,b

def fit_singleday_para(variable_data):
    '''

    y = a+b/x
    
    Parameters
    ----------
    variable_data : dataframe
        columns:x,y for fitness

    Returns
    -------
    a,b: paras of fitness.

    '''
    # （2）数据处理
    variable_data['y0'] = variable_data['y']
    variable_data = variable_data[variable_data['x']<=250].reset_index(drop=True)   # 平价小于250
    variable_data = variable_data[variable_data['y']<=300].reset_index(drop=True)   # 转股溢价率小于300%
    
    # （3）初始回归
    a,b=get_inverse_para(variable_data[['x','y']])
    variable_data['y_pre'] = a + b/variable_data['x']
    R2=((variable_data['y_pre']-variable_data['y'].mean())**2).sum()/((variable_data['y']-variable_data['y'].mean())**2).sum()
    # check_fitness(variable_data,R2)
    
    # （4）修改异常值，进行回归迭代
    R2_0 = 0.1
    # 拟合优度高于90%或者提升空间很低的时候不再迭代
    while ((R2/R2_0-1)>0.01) & (R2<=0.9):
        # 残差高于90%分位数的点定义为异常值
        tmpindex = variable_data[abs(variable_data['y']-variable_data['y_pre'])>abs(variable_data['y']-variable_data['y_pre']).quantile(0.9)].index
        # 修改异常值
        variable_data.loc[tmpindex,'y'] = (variable_data.loc[tmpindex,'y'] + variable_data.loc[tmpindex,'y_pre'])/2
        # 回归
        a,b = get_inverse_para(variable_data[['x','y']])
        # 查看回归效果
        variable_data['y_pre'] = a + b/variable_data['x']
        R2_0 = R2
        R2 = ((variable_data['y_pre']-variable_data['y'].mean())**2).sum()/((variable_data['y']-variable_data['y'].mean())**2).sum()
        # check_fitness(variable_data,R2)
    return pd.DataFrame([{'a':a,'b':b}])

# # 分析业绩
# def profit_analysis(pnl,begdate = None,enddate = None,
#                     plot_on = False, rf_rate=0.02,
#                     get_outcome = True):
#     '''
#     input: date,portfolio
#     '''
#     if begdate is not None:
#         pnl = pnl[pnl['date']>=pd.to_datetime(begdate)]
#     if enddate is not None:
#         pnl = pnl[pnl['date']<=pd.to_datetime(enddate)]       
#     if 'rf rate' not in pnl.columns.values:
#         pnl['rf rate'] = rf_rate 
#     if 'portfolio_ret' not in pnl.columns.values:
#         pnl['portfolio_ret'] = pnl['portfolio'].pct_change()
#         pnl['portfolio_ret'].fillna(0,inplace=True)
        

#     rf_rate = pnl['rf rate'].mean()
#     len_df = pnl.shape[0]

#     pnl['max_pnl'] = pnl['portfolio'].cummax()
#     pnl['drawdown'] = (pnl['portfolio'] - pnl['max_pnl']) / pnl['max_pnl']
    
#     # 绘制收益情况
#     if plot_on:
#         fig,ax1 = plt.subplots()
#         ax2 = ax1.twinx()
#         ax1.bar(pnl['date'],pnl['drawdown']) 
#         ax2.plot(pnl['date'],pnl['portfolio'])        
#         ax1.set_ylabel('drawdown')
#         ax2.set_ylabel('PNL')         
#         ax2.set_ylim(0.95,2)
#         ax1.set_ylim(-0.1,0)
    
#     # 年化收益
#     annual_ret = pnl['portfolio'].iloc[-1]**(250/len_df)-1
#     # 年化波动
#     annual_var = pnl['portfolio_ret'].var()*(len_df - 1)/len_df * 250
#     annual_std = np.sqrt(annual_var)    
#     # 最大回撤
#     max_drawdown = pnl['drawdown'].min()
#     # 夏普比率
#     sharpeRatio = (annual_ret - rf_rate) / annual_std
#     # Calmar比率
#     calmarRatio = -annual_ret / max_drawdown
#     # 胜率
#     pnl['week'] = pnl['date'].apply(lambda x : pd.to_datetime(x).strftime('%W'))
#     pnl['year'] = pnl['date'].apply(lambda x : x.year)
#     pnl_w = pnl.groupby(['year','week']).last()
#     pnl_w['portfolio_ret'] = pnl_w['portfolio'].pct_change()
#     winningRatio = (pnl_w['portfolio_ret']>0).astype('int').sum()\
#                     /pnl_w['portfolio_ret'].dropna().count()
#     print('*'*10 + ' ' + pnl['date'].min().strftime('%Y/%m/%d')+\
#           ' - '+pnl['date'].max().strftime('%Y/%m/%d') + ' ' + '*'*10)
#     print('total return: '+str(round(pnl['portfolio'].iloc[-1]*100-100,3)) + '%')
#     print('annual return: '+str(round(annual_ret*100,3)) + '%')
#     # print('annual variance: '+str(round(annual_var*100,3)) + '%')
#     print('annual std: '+str(round(annual_std*100,3)) + '%')
#     print('max drawdown: '+str(round(max_drawdown*100,3)) + '%')
#     print('sharpe ratio: '+str(round(sharpeRatio,3)))
#     print('Calmar ratio: '+str(round(calmarRatio,3)))
#     print('Winning ratio: '+str(round(winningRatio*100,3))+'%')
    
#     outcome = {'total return':pnl['portfolio'].iloc[-1]-1,
#                'annual return':annual_ret,
#                'annual std':annual_std,
#                'max drawdown':max_drawdown,
#                'sharpe ratio':sharpeRatio,
#                'Calmar ratio':calmarRatio,
#                'Winning ratio':winningRatio}
#     if get_outcome:
#         return outcome


# 计算每个分组中的排序
def get_rank(anadata,colname = 'PremiumRate',rankname = 'rank'):
    anadata[rankname] = anadata.sort_values(by=['date','group',colname])\
            .groupby(['date','group']).cumcount()
    # anadata = anadata.sort_values(by=['date','code']).reset_index(drop=True)
    return anadata





# %% 数据准备
# (1)时间范围
begdate = datetime.datetime(2016,1,1)
enddate = datetime.datetime.now() - d1

print('时间范围：')
print('开始时间 ' + begdate.strftime('%Y-%m-%d'))
print('结束时间 ' + enddate.strftime('%Y-%m-%d'))



# (2)数据提取
conn_zs = pymssql.connect(host=server_zs, database="DataCenter", charset='cp936')
cursor_zs = conn_zs.cursor()
conn_jydb = pymssql.connect(server_jydb, user_jydb, password_jydb, 'JYDB', charset='cp936')
cursor_jydb = conn_jydb.cursor()



# indinames = \
#     [ 'CBPrice','ParPrice','BondValue_wind','CBPrice_BS',
#     'ConvetPrice','StockPrice','ret','BS_minus_CBPrice',
#     'RemainScale',
#     'yield','YearsToMaturity2','std_stock','d1','d2',
#     'nd1','nd2','callPrice_raw','callPrice',
    
#     'YTM_wind','PremiumRate','Premium2BondValue','ConvetRatio',
    
#     'convertProbability','yield_toStock','HoldingReturn','StockValue2BondValue']



indinames = \
    [ 'CBPrice','ParPrice','BondValue_wind', 'ConvetPrice','ret','StockPrice',
    
    'RemainScale',
    'callPrice',
    
    'YTM_wind','PremiumRate','Premium2BondValue','ConvetRatio',
    
    'yield_toStock',
    
    'CBPrice_1y_5', 'CBPrice_1y_20', 'CBPrice_1y_50', 'CBPrice_1y_80', 'CBPrice_1y_95',
    
    'YearsToMaturity2',
    
    'CBClause_Change_finalstate','CBClause_Call_finalstate'
    ]


'''
##########
# (1) 转债面板数据
##########
'''
print('(1) 转债面板数据')
anadata_raw = get_exsiteddata_from_FI_CBSeriesIndicator(cursor_zs, indicators = indinames, begdate=begdate, enddate=enddate)\
    .rename(columns={'SecuCode':'InnerCode'})

'''
##########
# (2) 转债截面数据
##########
'''
print('(2) 转债截面数据')
CBinfo_raw = get_CBinfo(cursor_jydb)


'''
##########
# (3)评级信息
部分转债没有评级，包括（1）2010年之前（到期）的部分转债，（2）定向转债，（3）可交债。
##########
'''
print('(3) 评级信息')
InnerCodes = list(CBinfo_raw['InnerCode'].unique())
ratingData = get_ratingdata(cursor_jydb, InnerCodes)


'''
##########
# (4) 中证转债指数
##########
'''
print('(4) 中证转债指数')
zzzz_data = get_ZZZZ_data(cursor_jydb,begdate = begdate)

'''
##########
# (5) 到期日
##########
'''
print('(5) 转债上市日与到期日')
enddateData = get_enddatedata(cursor_jydb)


'''
##########
# (5) 行业信息
##########
'''
# 因为行业有可能会变，所以是面板数据
print('(5) 行业信息')
IndustryInfo = get_IndustryInfo(cursor_jydb) 




'''
##########
# 提取浙商价值股指数持仓
##########
'''
print('(6) 浙商价值股指数持仓')
'''提取浙商价值股指数持仓'''
def get_zsindex_holding_data(cursor):
    str_columns = 'IndexCode, SecCode, Weight, \
        IsSimulation, EffectiveDate'.replace(' ','')  # 删除所有空格
    list_columns = str_columns.split(',')

    str_sql = '''
            SELECT %s
              FROM [AMS].[dbo].[ZSIndex_Weight] 
              where IndexCode = 'ZS666666' AND IsSimulation = 0 
              ORDER BY EffectiveDate DESC 
            '''%(str_columns)

    cursor.execute(str_sql)
    
    tmp_table = pd.DataFrame(cursor.fetchall(), columns = list_columns)            

    return tmp_table

# 数据是日频调仓，买入评级会比持有评级的推荐持仓少一些

ValueBond = get_zsindex_holding_data(cursor_zs)
ValueBond = ValueBond[['SecCode', 'Weight', 'EffectiveDate']].rename(columns ={'EffectiveDate':'date'})

# 将持仓转为评级推荐数据
tmpdata = ValueBond.groupby('date')['Weight'].mean().reset_index().rename(columns = {'Weight':'mean'})
ValueBond = pd.merge(ValueBond, tmpdata, on='date')
ValueBond['zsscore'] = 1
tmpindex = ValueBond[ValueBond['Weight'] >= ValueBond['mean']].index
ValueBond.loc[tmpindex, 'zsscore'] = 2
ValueBond = ValueBond.astype({'date':'<M8[ns]'})




# 关闭服务器
cursor_zs.close()
conn_zs.close()

cursor_jydb.close()
conn_jydb.close()


'''
##########
# (5) 对照曲线
##########
'''
# 对照曲线
cmp_data = zzzz_data.rename(columns={'close':'basis_PNL'}).astype({'date':'<M8[ns]'})
cmp_data = cmp_data[(cmp_data['date']>=begdate) & (cmp_data['date']<=enddate)]

'''
##########
#  信用风险打分
##########
'''
# crdata = pd.read_excel('信用风险打分.xlsx')
# crdata = crdata[['转债代码',2018,2019,2020,2021,2022,2023,2024]]
# crdata = crdata.rename(columns={'转债代码':'BondCode_wind'})

# crdata2 = pd.read_excel('信用风险打分2.xlsx')
# crdata2 = crdata2[['转债代码','财报日期','打分','大股东股份质押比率','资产负债率','余额/市值']]
# crdata2 = crdata2.rename(columns={'转债代码':'BondCode_wind',
#                                   '财报日期':'date',
#                                   '打分':'creditScore',
#                                   '大股东股份质押比率':'StakingRatio',
#                                   '资产负债率':'Debt2asset',
#                                   '余额/市值':'RemainScale2MarketCap'})

'''
##########
#  行业板块信息
##########
'''
# 板块分类数据准备
ind_group = \
[['TMT', '通信', 'CI005026', '弹性'],
 ['TMT', '计算机', 'CI005027', '弹性'],
 ['TMT', '传媒', 'CI005028', '弹性'],
 ['电力设备及新能源', '电力设备', 'xxxx', '弹性'],
 ['电力设备及新能源', '电力设备及新能源', 'CI005011', '弹性'],
 ['电子', '电子', 'CI005025', '弹性'],
 ['电子', '电子元器件', 'xxxxx', '弹性'],
 ['有色金属', '有色金属', 'CI005003', '弹性'],
 ['汽车', '汽车', 'CI005013', '弹性'],
 ['机械', '机械', 'CI005010', '弹性'],
 
 ['国防军工', '国防军工', 'CI005012', '弹性'], 
 
 
 ['金融', '非银行金融', 'CI005022', '稳健'],
 ['金融', '银行', 'CI005021', '稳健'],
 
 
 ['家电', '家电', 'CI005016', '消费'],
 ['畜牧养殖', '畜牧养殖', 'xxxx', '消费'],
 ['美容护理', '美容护理', 'xxxx', '消费'],
 ['食品饮料', '食品饮料', 'CI005019', '消费'], 
 ['医药', '医药', 'CI005018', '消费'], 
 ['其他消费', '农林牧渔', 'CI005020', '消费'], 
 ['其他消费', '商贸零售', 'CI005014', '消费'],
 ['其他消费', '纺织服装', 'CI005017', '消费'],
 ['其他消费', '消费者服务', 'CI005015', '消费'],
 ['其他消费', '轻工制造', 'CI005009', '消费'],
 ['其他消费', '餐饮旅游', 'xxxx', '消费'],
 
 ['顺周期', '煤炭', 'CI005002', '周期'],
 ['顺周期', '钢铁', 'CI005005', '周期'],
 ['顺周期', '建材', 'CI005008', '周期'],
 ['顺周期', '建筑', 'CI005007', '周期'],
 ['顺周期', '家居家具', 'xxxx', '周期'],
 ['顺周期', '房地产', 'CI005023', '周期'], 
 ['石油石化', '石油石化', 'CI005001', '周期'], 
 ['基础化工', '基础化工', 'CI005006', '周期'],   
 ['交通运输', '交通运输', 'CI005024', '周期'],
 ['电力及公用事业', '电力及公用事业', 'CI005004', '周期'], 
 
 ['其他', '综合金融', 'CI005030', '其他']]


ind_df = pd.DataFrame(ind_group, columns = ['CBinduName', 'FirstIndustryName', '代码', 'Sector'])



'''
##########
#  基金持仓分析
##########
'''

# fundholding1 = pd.read_excel('底仓转债.xlsx', sheet_name='稳健型基金底仓')\
#     [['报告期','债券代码','正股代码','作为底仓基金数（只）','底仓持仓总市值（元）','全市场基金持仓总市值（元）']]\
#         .rename(columns = {'报告期':'date',
#                            '债券代码':'BondCode_wind',
#                            '正股代码':'SecuCode',
#                            '作为底仓基金数（只）':'fundNUM_s',
#                            '底仓持仓总市值（元）':'fundAUM5_s',
#                            '全市场基金持仓总市值（元）':'fundAUM_all_s'})
# fundholding2 = pd.read_excel('底仓转债.xlsx', sheet_name='进取型基金底仓')\
#     [['报告期','债券代码','正股代码','作为底仓基金数（只）','底仓持仓总市值（元）','全市场基金持仓总市值（元）']]\
#         .rename(columns = {'报告期':'date',
#                            '债券代码':'BondCode_wind',
#                            '正股代码':'SecuCode',
#                            '作为底仓基金数（只）':'fundNUM_f',
#                            '底仓持仓总市值（元）':'fundAUM5_f',
#                            '全市场基金持仓总市值（元）':'fundAUM_all_f'})

# fundholding = pd.merge(fundholding1,fundholding2,on=['date','BondCode_wind','SecuCode'],how = 'outer')

# fundholding = fundholding.fillna(0)
# fundholding['fundNUM'] = fundholding['fundNUM_f'] + fundholding['fundNUM_s']
# fundholding['fundAUM5'] = fundholding['fundAUM5_f'] + fundholding['fundAUM5_s']
# fundholding['fundAUM_all'] = fundholding['fundAUM_all_f'] + fundholding['fundAUM_all_s']

# # 公布时间为15个工作日：粗算为增加3周
# fundholding['repodate'] = fundholding['date'] + d1*21


'''
##########
#  数据整理
##########
'''
print('(7) 数据整理')
CBinfo = pd.merge(CBinfo_raw,enddateData,how = 'left',on = 'InnerCode')    # 到期日信息
anadata = pd.merge(anadata_raw.astype({'InnerCode':int}), CBinfo, on = 'InnerCode', how='left')  # 面板数据

# 行业信息
anadata = pd.merge_asof(anadata.sort_values(by='date').astype({'StockInnerCode':'int64','CompanyCode':'int64',}),
                      IndustryInfo.drop(columns=['AStockCode']).sort_values(by='InfoPublDate'),
                      left_on='date',right_on='InfoPublDate',by='CompanyCode')

# 增加浙商权益推荐打分
anadata = pd.merge(anadata, 
                ValueBond[['date', 'SecCode', 'zsscore']].rename(columns = {'SecCode':'SecuCode'}), 
                on=['date','SecuCode'],
                how='left')

# 增加评级信息
anadata = pd.merge_asof(anadata.astype({'InnerCode':'int64'}), ratingData.sort_values(by = ['date']).dropna(), by='InnerCode',on='date')

# 剔除平价为0的数据
anadata = anadata[anadata['ParPrice'].notnull()].reset_index(drop=True)

# # 增加浙商证券固收信用指标
# anadata = pd.merge_asof(anadata, crdata2, by='BondCode_wind', on = 'date')



'''# 增加行业板块特征'''
# 修改部分行业的中信Ⅰ级行业名称

# 其中：公用事业的"其他发电“需要计入”电力设备及新能源“
tmpindex = anadata[(anadata['ThirdIndustryName'] == '其他发电') |\
                   (anadata['ThirdIndustryName'] == '光伏设备') |\
                   (anadata['ThirdIndustryName'] == '锂电设备') |\
                   (anadata['ThirdIndustryName'] == '锂电化学品') |\
                   (anadata['ThirdIndustryName'] == '玻璃纤维') |\
                   (anadata['ThirdIndustryName'] == '玻璃') |\
                   (anadata['ThirdIndustryName'] == '膜材料')].index
anadata.loc[tmpindex, 'FirstIndustryName'] = '电力设备及新能源'   # 修改一级行业名称

# 其中：“电子化学品”需要计入“电子”
tmpindex = anadata[(anadata['ThirdIndustryName'] == '电子化学品')].index
anadata.loc[tmpindex, 'FirstIndustryName'] = '电子'   # 修改一级行业名称

# 其中：“家具”与“家居”需要计入新的一级行业
tmpindex = anadata[(anadata['ThirdIndustryName'] == '家具') |\
                   (anadata['ThirdIndustryName'] == '其他家居')].index
anadata.loc[tmpindex, 'FirstIndustryName'] = '家居家具'   # 修改一级行业名称
# 其中：提取生猪行业“畜牧养殖”计入新的一级行业
tmpindex = anadata[(anadata['ThirdIndustryName'] == '畜牧养殖')].index
anadata.loc[tmpindex, 'FirstIndustryName'] = '畜牧养殖'   # 修改一级行业名称
# 其中：提取“美容护理”计入新的一级行业
tmpindex = anadata[(anadata['ThirdIndustryName'] == '日用化学品')].index
anadata.loc[tmpindex, 'FirstIndustryName'] = '美容护理'   # 修改一级行业名称


anadata = pd.merge(anadata, ind_df[['CBinduName', 'FirstIndustryName','Sector']], left_on='FirstIndustryName', right_on='FirstIndustryName', how='left')


# # 增加基金重仓数据
# anadata = pd.merge_asof(anadata.sort_values(by='date'), 
#                         fundholding[['repodate','BondCode_wind','fundNUM','fundAUM5','fundAUM_all']].sort_values(by='repodate'), 
#                         left_on='date',
#                         right_on='repodate',
#                         by=['BondCode_wind'])


'''
##########
#  调整时间区间
##########
'''
begdate = anadata['date'].min()
enddate = anadata['date'].max()



# %% 修正估值因子更新
PremiumRate_adj_on =True
if PremiumRate_adj_on:
    
    # 每日模型拟合
    fit_data = anadata.rename(columns={'ParPrice':'x','PremiumRate':'y'}).groupby('date')\
        .apply(lambda x:fit_singleday_para(x))
    fit_data = fit_data.reset_index(level=1,drop=True) # 删去一个多余的index
    
    # 计算修正转股溢价率
    if 'a' in anadata.columns:
        anadata = anadata.drop(columns='a')
    
    if 'b' in anadata.columns:
        anadata = anadata.drop(columns='b')
    
    anadata = pd.merge(anadata,fit_data.reset_index(),on='date')
    
    anadata['PremiumRate_from_par'] = anadata['a'] + anadata['b']/anadata['ParPrice']
    anadata['PremiumRate_adj'] = anadata['PremiumRate'] - anadata['PremiumRate_from_par']
    
    # 修正转股溢价率越大，转债越高估



#  查看某一天的散点图

# tmpdate = datetime.datetime(2024,10,24)
# plotdata = anadata[anadata['date'] == tmpdate]

# plotdata = plotdata.sort_values('ParPrice')


# plt.figure()
# plt.scatter(plotdata['ParPrice'], plotdata['PremiumRate'])
# plt.plot(plotdata['ParPrice'],plotdata['PremiumRate_from_par'], color = 'tab:red')
# plt.title(tmpdate)




#  分组
'''
仅分析1~3组的可转债
0：70以下
1：70~90 √
2：90~110 √
3：110~130 √
4：130以上                                                                                                                                
'''
anadata['group_raw'] = ((anadata['ParPrice']+10)/20).astype(int)-3
anadata.loc[anadata[anadata['group_raw']<0].index,'group_raw'] = 0
anadata.loc[anadata[anadata['group_raw']>4].index,'group_raw'] = 4

anadata = anadata.sort_values(by=['InnerCode','date']).reset_index(drop=True)
anadata['group'] = anadata.groupby(['InnerCode'])['group_raw'].apply(lambda x: x.shift(1))
anadata['group'] = anadata['group'].fillna(2)

